*Monthly employment and wages by Sector (as defined by Mian and Sufi 2015)

*To run the  code, first download the singlefiles from the QCEW**********

****

capture erase county industry employmentINDMS.dta
forvalues yr = 2006 (1) 2009 {
  insheet using `yr'q1_q4singlefile.csv, clear
  keep if agglvl_code==76 | (industry=="10" & own==0)
  generate State = 1 if own_code== 1 | own_code==2 | own_code==3
    replace State = 0 if own_code==5
  destring industry_code, generate (industry) force
  keep if (industry>=10 & industry<10000) | industry==.
  
  merge m:1 industry using MSind
  
  gen Ind=tradcat
  replace Ind=0    if industry==10
   
  collapse (sum) month1_emplvl month2_emplvl month3_emplvl total_qtrly_wages taxable_qtrly_wages (mean) avg_wkly_wage, by( area_fips Ind year qtr)

  drop if Ind==. 
 
   capture append using "county industry employmentINDMS.dta"
  describe, short
   save "county industry employmentINDMS.dta", replace
     
 }

rename Ind ind
destring area_fips, replace force
rename area_fips fips
rename qtr quarter

*rename month3_emp emp
egen emp=rowmean(month1_emp month2_emp month3_emp)
rename avg_wkly wage
rename total_qtrly_wages inc

sort fips ind year quarter

drop if emp==0
drop if wage==0
  *drop month1 and 2 employment
drop month* taxable
drop if fips==.

reshape wide emp wage inc, i(fips year quarter ) j(ind)


destring fips, replace force
drop if fips==.

collapse (mean) emp* (sum) wage* inc*, by(fips year)

merge m:1 fips using countyCBSAmap.dta
keep if cbsa<.
collapse (sum)  inc*, by(cbsa year)

rename inc1 incTradable
rename inc2 incNonTrad
rename inc3 incConstruction
rename inc4 incOther


save CBSAempAnnMSrep.dta, replace

*****************************************************************************************
*Now run the below code to create MSstrict

*To run the code, first download the singlefiles from the QCEW**********
clear
capture erase "county industry employmentINDMSstrict.dta"
forvalues yr = 2006 (1) 2009 {
	insheet using `yr'q1_q4singlefile.csv, clear
	keep if agglvl_code==76 | (industry=="10" & own==0)

	destring industry_code, generate (industry) force
	keep if (industry>=10 & industry<10000) | industry==.
	
	merge m:1 industry using MSind
	
	gen Ind=1
  replace Ind=2    if nontradable==1
	replace Ind=0    if industry==10
		
		
  collapse (sum) month1_emplvl month2_emplvl month3_emplvl total_qtrly_wages taxable_qtrly_wages (mean) avg_wkly_wage, by( area_fips Ind year qtr)
	
	drop if Ind==. 
	
	 capture append using "county industry employmentINDMSstrict.dta"
	describe, short
	save "county industry employmentINDMSstrict.dta", replace
		 
 }
 
rename Ind ind
destring area_fips, replace force
rename area_fips fips
rename qtr quarter

*rename month3_emp emp
egen emp=rowmean(month1_emp month2_emp month3_emp)
rename avg_wkly wage
rename total_qtrly_wages inc

sort fips ind year quarter

save tempInd, replace
  ***will have emp and wage by industry, ownership code
drop if emp==0
drop if wage==0
  *drop month1 and 2 employment
drop month* taxable
drop if fips==.
reshape wide emp wage inc, i(fips year quarter ) j(ind)


destring fips, replace force
drop if fips==.

merge m:1 fips using countyCBSAmap.dta
keep if cbsa<.
collapse (sum) inc2, by(cbsa year)

rename inc2 incNonTradStrict


***merge MSstrict with MS and create Dinc variables*****
merge 1:1 cbsa year using CBSAempAnnMSrep
drop _merge


merge 1:1 cbsa year using QCEWannual.dta, keepusing(cbsa year inc0)
drop _merge
drop if year==.


reshape wide  inc* , i(cbsa) j(year)

foreach x in  NonTradStrict NonTrad Tradable Construction Other {
	gen Dinc`x'=((inc`x'2009+inc`x'2008)-(inc`x'2006+inc`x'2007))/(2*(inc02006+inc02007))
}

keep cbsa Dinc*
save DincMS
